package com.delete;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.commons.lang3.StringUtils;

import com.kevin.enums.DBType;


public  class DbBase_fordel{
	private final String propFile = "dbinfo.properties";
	public DBType dbType = DBType.MYSQL;
	public StringBuilder debugInfo = new StringBuilder();
	
	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;
    private  DatabaseMetaData metaData = null;
    
	private Properties prop = null;
	private InputStream propInfo = null;
	
	private String version = StringUtils.EMPTY;
	public static Map<String, String> versionCache = new ConcurrentHashMap<>();
	
	public DbBase_fordel(String dbconn) {		
		try {
			prop = new Properties();
			propInfo = this.getClass().getClassLoader().getResourceAsStream(propFile);	
			prop.load(propInfo);
			Class.forName(prop.getProperty(dbconn + ".Driver"));		
			conn = DriverManager.getConnection(prop.getProperty(dbconn + ".Url"),prop.getProperty(dbconn + ".UserName"),prop.getProperty(dbconn + ".Password"));
            stmt = conn.createStatement();
            metaData = conn.getMetaData();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				propInfo.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	public ResultSet ExeResultSetSQL(String sql) throws SQLException {
		rs = stmt.executeQuery(sql);
		return rs;
	}
	
    public char getPrefix()
    {
        return '@';
    }
	
    public String getVersion() {
    	if (StringUtils.isEmpty(version))
        {
            switch (dbType)
            {
                case TXT:
                    version = "txt2.0";
                    break;
                case XML:
                    version = "xml2.0";
                    break;
                default:
                    if (versionCache.containsKey(conn))
                    {
                        version = versionCache.get(conn);
                    }
                    else
                    {
//                        if (isOpenTrans && useConnBean.IsSlave)// && 事务操作时，如果在从库，切回主库
//                        {
//                            ResetConn(connObject.Master);
//                        }
//                        if (OpenCon(useConnBean, AllowConnLevel.MaterBackupSlave))//这里可能切换链接
//                        {
//                            version = _con.ServerVersion;
//                            if (!versionCache.containsKey(conn))
//                            {
//                                versionCache.Set(conn, version);
//                            }
//                            if (!isOpenTrans)//避免把事务给关闭了。
//                            {
//                                CloseCon();
//                            }
//                        }
                    }

                    break;
            }


        }
    	return version;
	}

    
	/**
	 * 获取数据表和备注
	 * @param metaData
	 * @return
	 * @throws SQLException
	 */
	public Map<String, String> getTables(DatabaseMetaData metaData) throws SQLException{
		Map<String, String> tableMap = new HashMap<>();
		String[] types = { "TABLE","VIEW" };  
		//数据库名称，数据库登录名，表名称，类型标准（"TABLE"、"VIEW"、"SYSTEM TABLE"、"GLOBAL TEMPORARY"、"LOCAL TEMPORARY"、"ALIAS" 和 "SYNONYM"）
		ResultSet rs = metaData.getTables(null, null, null, types);  
        while (rs.next()) {  
            String tableName = rs.getString("TABLE_NAME");  //表名  
            //String tableType = rs.getString("TABLE_TYPE");  //表类型  
            String remarks = rs.getString("REMARKS");       //表备注  
            tableMap.put(tableName, remarks);
        }
        return tableMap;
	}
    
	/**
	 * 获取数据表和备注
	 * @return
	 * @throws SQLException
	 */
	public Map<String, String> getTables() throws SQLException{
		return getTables(metaData);
	}
	
	public boolean IsPrimaryKey(String tableName,String columnName) throws SQLException {
		Map<String,Boolean> pKeys = getPrimaryKeys(tableName);
		return pKeys.get(columnName) == null?false:true;
	}
	
	/**
	 * 获取所有列
	 * @param metaData
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Map<String, Object>> getColumns(DatabaseMetaData metaData,String tableName) throws SQLException{
        Map<String,Map<String,Object>> columnProps = new HashMap<>();
        Map<String,Object> colProp = null;
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        Map<String,Boolean> pKeys = getPrimaryKeys(tableName);
        while(rs.next()){
            colProp = new HashMap<>();
            colProp.put("sqlType",rs.getInt("DATA_TYPE"));//对应的java.sql.Types的SQL类型(列类型ID)  
            colProp.put("description",rs.getString("REMARKS"));//列描述
            colProp.put("defaultValue",rs.getString("COLUMN_DEF"));//默认值  
            colProp.put("isCanNull",rs.getInt("NULLABLE") == 0?false:true);//是否允许为null  
            colProp.put("isAutoIncrement",Boolean.parseBoolean(rs.getString("IS_AUTOINCREMENT")));//指示此列是否是自动递增
            colProp.put("columnName",rs.getString("COLUMN_NAME"));//列名
            colProp.put("tableName",rs.getString("TABLE_NAME"));  //表名    
            colProp.put("maxSize",rs.getInt("COLUMN_SIZE"));//列大小 
            colProp.put("scale",rs.getInt("DECIMAL_DIGITS")); //小数位数   
            colProp.put("isPrimaryKey", pKeys.get(colProp.get("columnName")) == null?false:true);
            colProp.put("sqlTypeName",rs.getString("TYPE_NAME"));//java.sql.Types类型名称(列类型名称)  
            columnProps.put((String)colProp.get("columnName"),colProp);
        }   
        return columnProps;
    }
    
	/**
	 * 获取所有列
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Map<String, Object>> getColumns(String tableName) throws SQLException{
		return getColumns(metaData,tableName);
	}
	
    /**
     * 获取主键列名
     * @param tableName
     * @return
     * @throws SQLException 
     */
    private Map<String,Boolean> getPrimaryKeys(String tableName) throws SQLException{
     	Map<String,Boolean> primaryMap = new HashMap<>();
        ResultSet rs = metaData.getPrimaryKeys(null, null, tableName);
        while (rs.next()){ 
        	primaryMap.put(rs.getString("COLUMN_NAME"),true);
        }
        return primaryMap;
    }
	
    
    public void close(){
		//关闭资源(先开后关)  
        if(rs!=null)  
        {  
            try  
            {  
                rs.close();  
            }  
            catch(SQLException e)  
            {  
                e.printStackTrace();  
            }  
            rs=null;  
        }  
        if(conn!=null)  
        {  
            try  
            {  
            	conn.close();  
            }  
            catch(SQLException e)  
            {  
                e.printStackTrace();  
            }  
            conn=null;  
       }  
        if(null!=stmt)  
        {  
            try  
            {  
            	stmt.close();  
            }  
            catch(SQLException e)  
            {  
                e.printStackTrace();  
            }  
            stmt=null;  
        }  
	}
}